/*
 * <one line to give the program's name and a brief idea of what it does.>
 * Copyright (C) 2019
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * NOTE for future me:
 * 1) to speed up database and decrease size need calculate v_sum only when update build
 * rows with v_sum = 0 not yet calculated and must be saved
 * rows with v_sum > 0 and if min == max && v_sum < v_sum  of last calculated row can be deleted from database, run this check every N rows was calculated
 * if i'm right it will works and u can use in-memory db freely
 *
 * 2) also u can can use this this column to get uncalculated rows right from sqlite and drop queue, this is decrease ram usage, but can slow a bit
 * it can be usefull if using in-memory database
 *
 * 3) change pragmas and try single thread mode without locks as mutex used
 */

#include "dbsqlite.hpp"
#include "dblmdb.hpp"
#include "logger.h"

#include "sqlite/sqlite3.h"
#include <spdlog/spdlog.h>
#include "spdlog/fmt/ostr.h"

SQLStatement::SQLStatement(sqlite3 * db, const std::string & query) {
	m_Database = db;
	m_SQL = query;
	m_Log = Logger::get("SQLStmt");
}

SQLStatement::~SQLStatement() {
	if(m_Statement)
		sqlite3_finalize(m_Statement);
}

int SQLStatement::run() {
	if(!m_Statement)
		prepare();

	int result = sqlite3_step(m_Statement);

	switch(result) {
		case SQLITE_DONE:
		case SQLITE_ROW:
			break;

		default:
			m_Log->error("Error run : {}", sqlite3_errmsg(m_Database));
			break;
	}

	return result;
}

void SQLStatement::clear() {
	sqlite3_clear_bindings(m_Statement);
}

bool SQLStatement::bind(const uint32_t & index, const uint32_t & value) {
	if(!m_Statement)
		prepare();

	bool success = false;
	int result = sqlite3_bind_int(m_Statement, index, value);

	switch(result) {
		case SQLITE_OK:
			success = true;
			break;

		default:
			m_Log->error("Error bind #{} with {}: {}", index, value, sqlite3_errmsg(m_Database));

	}

	return success;
}

bool SQLStatement::prepare(const std::string & query) {
	if(!query.empty()) {
		// recreate statement if needed
		if(m_Statement) {
			sqlite3_finalize(m_Statement);
			m_Statement = nullptr;
		}

		m_SQL = query;
	}

	bool success = false;

	if(!m_Statement) {
		int ret = sqlite3_prepare_v2(m_Database, m_SQL.c_str(), m_SQL.size(), &m_Statement, 0);

		switch(ret) {
			case SQLITE_OK:
				success = true;
				break;

			default:
				m_Log->error("prepare failed #{}: {}", ret, sqlite3_errmsg(m_Database));
				break;
		}
	}

	return success;
}

bool SQLStatement::reset() {
	int ret = sqlite3_reset(m_Statement);
	bool success = false;

	switch(ret) {
		case SQLITE_OK:
			success = true;
			break;

		default:
			m_Log->error("reset failed #{}: {}", ret, sqlite3_errmsg(m_Database));
			break;
	}

	return success;
}

DBSQLite::DBSQLite(const std::string & dbname, bool rewrite) : m_DBName(dbname) {
	m_Log = Logger::get("SQLite");

	int ret = sqlite3_open(dbname.c_str(), &m_Database);

	switch(ret) {
		case SQLITE_OK:
			m_Log->debug("Opening database {}", m_DBName);
			pragma("main");
			init(rewrite);
			break;

		default:
			m_Log->error("failed open database '{}' #{}: {}", dbname, ret, sqlite3_errstr(ret));
	}
}

void DBSQLite::init(bool rewrite) {

	if(rewrite) {
		// table sturcture
		const std::string builds_drop = "DROP TABLE IF EXISTS `builds`";
		const std::string builds_create = "CREATE TABLE IF NOT EXISTS `builds` ( \
			`id` INTEGER PRIMARY KEY NOT NULL, `min` SMALLINT UNSIGNED NOT NULL, `max` SMALLINT UNSIGNED INT, \
			`v_1` SMALLINT UNSIGNED NOT NULL, `v_2` SMALLINT UNSIGNED NOT NULL, `v_3` SMALLINT UNSIGNED NOT NULL, `v_4` SMALLINT UNSIGNED NOT NULL, \
			`v_5` SMALLINT UNSIGNED NOT NULL, `v_6` SMALLINT UNSIGNED NOT NULL, `v_7` SMALLINT UNSIGNED NOT NULL, `v_8` SMALLINT UNSIGNED NOT NULL, \
			`v_9` SMALLINT UNSIGNED NOT NULL, `v_10` SMALLINT UNSIGNED NOT NULL, `v_11` SMALLINT UNSIGNED NOT NULL, `v_12` SMALLINT UNSIGNED NOT NULL \
			, `v_sum` SMALLINT UNSIGNED NOT NULL DEFAULT 0)";
		const std::string builds_index[] = {
			"CREATE UNIQUE INDEX idx_uniq ON `builds` ( `v_1`, `v_2`, `v_3`, `v_4`, `v_5`, `v_6`, `v_7`, `v_8`, `v_9`, `v_10`, `v_11`, `v_12` )",
			"CREATE INDEX idx_sum ON `builds` ( `v_sum` )",
			"CREATE INDEX idx_minmax ON `builds` ( `min`, `max` )"
		};

		sqlite3_exec(m_Database, builds_drop.c_str(), nullptr, nullptr, nullptr);
		sqlite3_exec(m_Database, builds_create.c_str(), nullptr, nullptr, nullptr);

		for(auto & i : builds_index) {
			sqlite3_exec(m_Database, i.c_str(), nullptr, nullptr, nullptr);
		}
	}

	// queries
	const std::string builds_add = "INSERT OR IGNORE INTO  `builds` \
		( `v_1`, `v_2`, `v_3`, `v_4`, `v_5`, `v_6`, `v_7`, `v_8`, `v_9`, `v_10`, `v_11`, `v_12`, `min`, `max` )  \
		VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	const std::string builds_update = "UPDATE `builds` SET `min` = ?, `max`= ?, `v_sum` = v_1 + v_2 + v_3 + v_4 + v_5 + v_6 + v_7 + v_8 + v_9 + v_10 + v_11 + v_12 WHERE \
		`v_1` = ? AND `v_2` = ? AND `v_3` = ? AND `v_4` = ? AND `v_5` = ? AND `v_6` = ?  AND \
		`v_7` = ? AND `v_8` = ? AND `v_9` = ? AND `v_10` = ? AND`v_11` = ? AND `v_12` = ?";
	const std::string builds_erase = "DELETE FROM `builds`";
	const std::string builds_size = "SELECT COUNT(1) FROM `builds`";

// 	m_Log->debug(builds_drop);
// 	m_Log->debug(builds_create);
// 	m_Log->debug(builds_index);
// 	m_Log->debug(builds_add);
// 	m_Log->debug(builds_update);

	m_Statements[BUILDS_UPDATE] = new SQLStatement(m_Database, builds_update);
	m_Statements[BUILDS_ADDS] = new SQLStatement(m_Database, builds_add);
	m_Statements[BUILDS_ERASE] = new SQLStatement(m_Database, builds_erase);
	m_Statements[BUILDS_SIZE] = new SQLStatement(m_Database, builds_size);
}

DBSQLite::~DBSQLite() {
	for(auto & i : m_Statements) {
		delete i.second;
	}

	sqlite3_close(m_Database);
	m_Log->debug("Closing database {}", m_DBName);
}

bool DBSQLite::empty() {
	return DBSQLite::size() == 0;
}

std::size_t DBSQLite::size() {
	std::lock_guard<std::mutex> lock(m_Mutex);
	SQLStatement * stmt = m_Statements[BUILDS_SIZE];
	std::size_t size = 0;
	int ret = stmt->run();

	switch(ret) {
		case SQLITE_ROW:
			size = sqlite3_column_int(stmt->raw(), 0);
			break;

		default:
			m_Log->error("fail get table size #{}: {}", ret, sqlite3_errmsg(m_Database));
	}

	stmt->reset();
	return size;
}

void DBSQLite::erase() {
	std::lock_guard<std::mutex> lock(m_Mutex);
	SQLStatement * stmt = m_Statements[BUILDS_ERASE];
	int ret = stmt->run();

	switch(ret) {
		case SQLITE_DONE:
			break;

		default:
			m_Log->error("failed erase table #{}: {}", ret, sqlite3_errmsg(m_Database));
			break;
	}

	stmt->reset();
}

bool DBSQLite::updateBuild(PathData * data) {
	std::lock_guard<std::mutex> lock(m_Mutex);
	return updateBuildSQLite(data);
}

std::size_t DBSQLite::addBuilds(std::vector<PathData> * data, PathData * build, DataBase * queue) {
	std::lock_guard<std::mutex> lock(m_Mutex);

	std::size_t count = 0;
	int ret;

	if(build) {
		updateBuildSQLite(build);
	}

	for(auto & i : *data) {
		if(addBuild(&i)) {
			count++;
			queue->put(&i, sizeof(i));
		}
	}

	return count;
}

bool DBSQLite::addBuild(PathData * data) {
	SQLStatement * stmt = m_Statements[BUILDS_ADDS];

	// bind data
	for(auto j = 0; j < upgradeCount; ++j)
		stmt->bind(j + 1, data->ship.data[j]);

	stmt->bind(13, data->ai[0]);
	stmt->bind(14, data->ai[1]);
	// run query
	int ret = stmt->run();
	bool success = false;

	switch(ret) {
		case SQLITE_DONE:
			if(sqlite3_changes(m_Database) == 1) {
				success = true;
			}

			break;

		default:
			m_Log->error("fail add build #{}: {}", ret, sqlite3_errmsg(m_Database));
			break;
	}

	stmt->clear();
	stmt->reset();
	return success;
}

bool DBSQLite::updateBuildSQLite(PathData * data) {
	SQLStatement * stmt = m_Statements[BUILDS_UPDATE];
	stmt->bind(1, data->ai[0]);
	stmt->bind(2, data->ai[1]);

	for(auto i = 0; i < upgradeCount; ++i) {
		stmt->bind(i + 3, data->ship.data[i]);
	}

	bool success = false;

	int ret = stmt->run();

	switch(ret) {
		case SQLITE_DONE:
			ret = sqlite3_changes(m_Database);

			if(ret == 1) {
				success = true;
			} else if(ret == 0) {
				m_Log->info("Build not exists, adding...");
				success = addBuild(data);
			} else {
				// its must be impossible, or unique index not exists.
				m_Log->error("updated {} rows instead 1", ret);
			}

			break;

		default:
			m_Log->error("update failed #{}: {}", ret, sqlite3_errmsg(m_Database));
			break;
	}

	stmt->clear();
	stmt->reset();

	return success;
}

void DBSQLite::findPathCommon(DataBase * db) {
	std::lock_guard<std::mutex> lock(m_Mutex);
	// find end point
	std::string query = "SELECT `id`, (SELECT MIN(min) FROM `builds`), `min`, `max` FROM `builds` WHERE `max` = (SELECT MAX(max) FROM `builds`) ORDER BY `max` DESC, `min` ASC limit 1";
	SQLStatement stmt(m_Database, query);
	stmt.run();
	uint32_t endData[4];

	for(auto i = 0; i < 4; ++i) {
		endData[i] = sqlite3_column_int(stmt.raw(), i);
	}

	stmt.clear();
	stmt.reset();
	m_Log->info("Building path from {} to {}...", endData[1], endData[3]);

	// create path
	// TODO maybe add sort by upgrades? check limit
	// пересмотри сортировку?
	query = "SELECT t1.id, t1.min, t1.max FROM builds as t1, builds as t2 WHERE t2.id = ? AND t1.max >= ? \
			AND t1.v_1 <= t2.v_1 AND t1.v_2 <= t2.v_2 AND t1.v_3 <= t2.v_3 AND t1.v_4 <= t2.v_4 \
			AND t1.v_5 <= t2.v_5 AND t1.v_6 <= t2.v_6 AND t1.v_7 <= t2.v_7 AND t1.v_8 <= t2.v_8 \
			AND t1.v_9 <= t2.v_9 AND t1.v_10 <= t2.v_10 AND t1.v_11 <= t2.v_11 AND t1.v_12 <= t2.v_12 \
			ORDER BY t1.min ASC, t1.id ASC LIMIT 1";
	stmt.prepare(query);
	std::vector<uint32_t> jumpPoints;
	findPathRecursive(endData[0], endData[2], endData[1], &jumpPoints, &stmt);

	if(jumpPoints.empty()) {
		m_Log->error("Cannot find path from {} to {}", endData[2], endData[3]);
	} else {
		m_Log->info("Jump from {} to {}", endData[2], endData[3]);
		jumpPoints.push_back(endData[0]);
	}

	// get data for each point in data
	query = "SELECT `v_1`, `v_2`, `v_3`, `v_4`, `v_5`, `v_6`, `v_7`, `v_8`, `v_9`, `v_10`, `v_11`, `v_12`, `min`, `max` FROM `builds` WHERE `id` = ? ";
	stmt.prepare(query);
	PathData path;

	for(auto it = jumpPoints.rbegin(); it != jumpPoints.rend(); ++it) {

		stmt.bind(1, *it);

		switch(stmt.run()) {
			case SQLITE_ROW:
				for(auto i = 0; i < upgradeCount; ++i) {
					path.ship.data[i] = sqlite3_column_int(stmt.raw(), i);
				}

				path.ai[0] = sqlite3_column_int(stmt.raw(), 12);
				path.ai[1] = sqlite3_column_int(stmt.raw(), 13);
				db->put(&path, sizeof(path));
				m_Log->info("Loading data for point #{:9d}: {}", *it, path);
				break;

			default:
				// if we here something strange happends
				m_Log->critical("Database corrupt!");
				db->erase();
				break;
		}

		stmt.clear();
		stmt.reset();
	}
}

bool DBSQLite::findPathRecursive(const uint32_t id, const uint16_t max, const uint16_t stop, std::vector<uint32_t> * path, SQLStatement * stmt) {
	stmt->bind(1, id);
	stmt->bind(2, max);
	stmt->run();
	uint32_t nextID = sqlite3_column_int(stmt->raw(), 0);
	uint16_t nextMax = sqlite3_column_int(stmt->raw(), 1);
	stmt->clear();
	stmt->reset();

	if(nextID == id) {
		// something goes wrong
		m_Log->info("Stuck on id {}. Database corrupt?", id);
		return false;
	}

	if(nextMax == stop || findPathRecursive(nextID, nextMax, stop, path, stmt)) {
		// add this point to path!
		path->push_back(nextID);
		m_Log->info("Jump from {} to {}", nextMax, max);
		return true;
	}

	return false;
}

std::size_t DBSQLite::copyDataTo(const std::string & dbname) {
	std::lock_guard<std::mutex> lock(m_Mutex);
	std::string query = "ATTACH DATABASE '" + dbname + "' AS path";

	SQLStatement stmt(m_Database, query);
	std::size_t count = 0;

	switch(stmt.run()) {
		case SQLITE_DONE:
			pragma("path");
			query = "INSERT INTO `path`.`builds` (min, max, v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10, v_11, v_12) \
				SELECT min, max, v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10, v_11, v_12 FROM `builds`";
			stmt.reset();
			stmt.prepare(query);

			if(stmt.run() != SQLITE_DONE) {
				m_Log->error("failed copy data to {}: {}", dbname, sqlite3_errmsg(m_Database));
			} else {
				count = sqlite3_changes(m_Database);
				m_Log->info("Copy {} record to {}", count, dbname);
			}

			break;

		default:
			m_Log->error("fail attach database {}: {}", dbname, sqlite3_errmsg(m_Database));
			break;
	}

	// detach
	stmt.prepare("DETACH DATABASE path");
	stmt.run();
	return count;
}

std::size_t DBSQLite::moveUnuseDataTo(const std::string & dbname){
	std::lock_guard<std::mutex> lock(m_Mutex);
	std::string query = "ATTACH DATABASE '" + dbname + "' AS path";

	SQLStatement stmt(m_Database, query);
	std::size_t count = 0;

	switch(stmt.run()) {
		case SQLITE_DONE:
			pragma("path");
			query = "INSERT INTO `path`.`builds` (min, max, v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10, v_11, v_12) \
				SELECT min, max, v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10, v_11, v_12 FROM `main`.`builds` as `b1` \
				WHERE b1.v_sum > 0 AND EXISTS ( SELECT 1 FROM builds as b2 WHERE b2.v_sum > b1.v_sum )";
			stmt.reset();
			stmt.prepare(query);

			if(stmt.run() != SQLITE_DONE) {
				m_Log->error("failed copy data to {}: {}", dbname, sqlite3_errmsg(m_Database));
			} else {
				count = sqlite3_changes(m_Database);
				m_Log->debug("Move {} record to {}", count, dbname);

				// delete all this rows
				query = "DELETE FROM main.builds as b1 \
					WHERE b1.v_sum > 0 AND EXISTS ( SELECT 1 FROM builds as b2 WHERE b2.v_sum > b1.v_sum )";

				stmt.reset();
				stmt.prepare(query);

				if( stmt.run() != SQLITE_DONE ){
					m_Log->error("failed to detele moved builds: {}", sqlite3_errmsg(m_Database));
				} else {
					std::size_t countDeleted = sqlite3_changes(m_Database);
					m_Log->debug("Delete {} records", countDeleted);
					if( countDeleted != count ){
						m_Log->error("Moved {} rows, delete {} rows!", count, countDeleted);
					}
				}
			}

			break;

		default:
			m_Log->error("fail attach database {}: {}", dbname, sqlite3_errmsg(m_Database));
			break;
	}

	// detach
	stmt.prepare("DETACH DATABASE path");
	stmt.run();

	// vacuum
	stmt.prepare("VACUUM");
	stmt.run();
	return count;
}

std::size_t DBSQLite::moveFromLMDB(DBLMDB * db) {
	std::lock_guard<std::mutex> lock(m_Mutex);
	PathData data;
	std::size_t count = 0;

	while(db->getBuild(&data)) {
		addBuild(&data);
		count++;
	}

	m_Log->info("Move {} records", count);

	return count;
}

void DBSQLite::prepareDB(const std::string & dbname) {
	DBSQLite * db = new DBSQLite(dbname);
	db->erase();
	delete db;
}

void DBSQLite::pragma(const std::string & dbname) {
	/**
	 *  TODO single thread mode
	 * TODO SQLITE_TEMP_STORE
	 * TODO mmap size check
	 */
	const std::string pragmas[] = {
		"PRAGMA " + dbname + ".synchronous = OFF",
		"PRAGMA " + dbname + ".journal_mode = OFF",
		"PRAGMA " + dbname + ".locking_mode = EXCLUSIVE",
		"PRAGMA " + dbname + ".page_size = 40960",
		"PRAGMA " + dbname + ".cache_size = -20000",
		"PRAGMA temp_store = MEMORY",
		"PRAGMA " + dbname + ".mmap_size = 1073741824",
		"PRAGMA " + dbname + ".auto_vacuum = FULL"
	};

	char * err = nullptr;

	for(const auto & i : pragmas) {
		sqlite3_exec(m_Database, i.c_str(), nullptr, nullptr, &err);

		if(err) {
			m_Log->error("pragma '{}' failed: {}", i, err);
			sqlite3_free(err);
		}
	}
}

std::size_t DBSQLite::removeZeroWins() {
	std::lock_guard<std::mutex> lock(m_Mutex);
	std::size_t count = 0;
	const std::string query = "DELETE FROM `builds` WHERE min = max";
	SQLStatement stmt(m_Database, query);

	switch(stmt.run()) {
		case SQLITE_DONE:
			count = sqlite3_changes(m_Database);
			break;

		default:
			m_Log->error("fail deleting rows: {}", sqlite3_errmsg(m_Database));
			break;
	}

	return count;
}

std::size_t DBSQLite::removeZeroWinsPartial() {
	std::lock_guard<std::mutex> lock(m_Mutex);
	std::size_t count = 0;
	const std::string query = "DELETE FROM builds as b1 WHERE b1.min = b1.max AND b1.v_sum > 0 AND EXISTS ( SELECT 1 FROM builds as b2 WHERE b2.v_sum > b1.v_sum )";
	SQLStatement stmt(m_Database, query);

	switch(stmt.run()) {
		case SQLITE_DONE:
			count = sqlite3_changes(m_Database);
			break;

		default:
			m_Log->error("fail partial deleting rows: {}", sqlite3_errmsg(m_Database));
			break;
	}

	return count;
}

// TODO ALERT
bool DBSQLite::get(void * data, const std::size_t & size) {
	return false;
}

void DBSQLite::put(void * data, const std::size_t & size) {
}



